xplly'Blog

切换主题

一、基础知识 ​

1.1 架构 ​

整个MySQL Server由以下组成

  • Connection Pool : 连接池组件
  • Management Services & Utilities : 管理服务和工具组件
  • SQL Interface : SQL接口组件
  • Parser : 查询分析器组件
  • Optimizer : 优化器组件
  • Caches & Buffers : 缓冲池组件
  • Pluggable Storage Engines : 存储引擎
  • File System : 文件系统

1） 连接层

最上层是一些客户端和链接服务，包含本地sock 通信和大多数基于客户端/服务端工具实现的类似于 TCP/IP的通信。主要完成一些类似于连接处理、授权认证、及相关的安全方案。在该层上引入了线程池的概念，为通过认证安全接入的客户端提供线程。同样在该层上可以实现基于SSL的安全链接。服务器也会为安全接入的每个客户端验证它所具有的操作权限。

2） 服务层

第二层架构主要完成大多数的核心服务功能，如SQL接口，并完成缓存的查询，SQL的分析和优化，部分内置函数的执行。所有跨存储引擎的功能也在这一层实现，如 过程、函数等。在该层，服务器会解析查询并创建相应的内部解析树，并对其完成相应的优化如确定表的查询的顺序，是否利用索引等， 最后生成相应的执行操作。如果是select语句，服务器还会查询内部的缓存，如果缓存空间足够大，这样在解决大量读操作的环境中能够很好的提升系统的性能。

3） 引擎层

存储引擎层， 存储引擎真正的负责了MySQL中数据的存储和提取，服务器通过API和存储引擎进行通信。不同的存储引擎具有不同的功能，这样我们可以根据自己的需要，来选取合适的存储引擎。

4）存储层

数据存储层， 主要是将数据存储在文件系统之上，并完成与存储引擎的交互。

和其他数据库相比，MySQL有点与众不同，它的架构可以在多种不同场景中应用并发挥良好作用。主要体现在存储引擎上，插件式的存储引擎架构，将查询处理和其他的系统任务以及数据的存储提取分离。这种架构可以根据业务的需求和实际需要选择合适的存储引擎。

1.2 存储引擎 ​

存储引擎就是存储数据，建立索引，更新查询数据等等技术的实现方式 。存储引擎是基于表的，而不是基于库的。所以存储引擎也可被称为表类型。

​ Oracle，SqlServer等数据库只有一种存储引擎。MySQL提供了插件式的存储引擎架构。所以MySQL存在多种存储引擎，可以根据需要使用相应引擎，或者编写存储引擎。

​ MySQL5.0支持的存储引擎包含 ： InnoDB 、MyISAM 、BDB、MEMORY、MERGE、EXAMPLE、NDB Cluster、ARCHIVE、CSV、BLACKHOLE、FEDERATED等，其中InnoDB和BDB提供事务安全表，其他存储引擎是非事务安全表。

可以通过指定 show engines ， 来查询当前数据库支持的存储引擎 ：

创建新表时如果不指定存储引擎，那么系统就会使用默认的存储引擎，MySQL5.5之前的默认存储引擎是MyISAM，5.5之后就改为了InnoDB。

查看Mysql数据库默认的存储引擎 ， 指令 ：

 show variables like '%storage_engine%' ；

存储引擎对比 ​

特点InnoDBMyISAMMEMORYMERGENDB
存储限制64TB有有没有有
事务安全==支持==
锁机制==行锁(适合高并发)====表锁==表锁表锁行锁
B树索引支持支持支持支持支持
哈希索引支持
全文索引支持(5.6版本之后)支持
集群索引支持
数据索引支持支持支持
索引缓存支持支持支持支持支持
数据可压缩支持
空间使用高低N/A低低
内存使用高低中等低高
批量插入速度低高高高高
支持外键==支持==

InnoDB ​

InnoDB存储引擎是Mysql的默认存储引擎。InnoDB存储引擎提供了具有提交、回滚、崩溃恢复能力的事务安全。但是对比MyISAM的存储引擎，InnoDB写的处理效率差一些，并且会占用更多的磁盘空间以保留数据和索引。

InnoDB 行锁 操作时,只锁某一行,不对其它行有影响 适合高并发的操作

InnoDB:不仅缓存索引,还要缓存真实数据,对内存要求比较高,而且内存大小对性能有决定性的影响

InnoDB存储引擎不同于其他存储引擎的特点 ：

事务控制

sql
create table goods_innodb(
	id int NOT NULL AUTO_INCREMENT,
	name varchar(20) NOT NULL,
    primary key(id)
)ENGINE=innodb DEFAULT CHARSET=utf8;

-- 修改已存在的表的引擎
alter table goods_innodb ENGINE = innoDB;
sql
start transaction;

insert into goods_innodb(id,name)values(null,'Meta20');

commit;

外键约束

​ MySQL支持外键的存储引擎只有InnoDB ， 在创建外键的时候， 要求父表必须有对应的索引 ， 子表在创建外键的时候， 也会自动的创建对应的索引。

外键信息可以使用如下两种方式查看 ：

show create table city_innodb ;

存储方式

InnoDB 存储表和索引有以下两种方式 ：

①. 使用共享表空间存储， 这种方式创建的表的表结构保存在.frm文件中， 数据和索引保存在 innodb_data_home_dir 和 innodb_data_file_path定义的表空间中，可以是多个文件。

②. 使用多表空间存储， 这种方式创建的表的表结构仍然存在 .frm 文件中，但是每个表的数据和索引单独保存在 .ibd 中。

MyISAM ​

MyISAM 不支持事务、也不支持外键，其优势是访问的速度快，对事务的完整性没有要求或者以SELECT、INSERT为主的应用基本上都可以使用这个引擎来创建表 。

MyISAM 表锁 操作一条记录也会锁住整个表 不适合高并发的操作，

MyISAM : 只缓存索引,不缓存数据，

有以下两个比较重要的特点：

不支持事务

sql
create table goods_myisam(
	id int NOT NULL AUTO_INCREMENT,
	name varchar(20) NOT NULL,
    primary key(id)
)ENGINE=myisam DEFAULT CHARSET=utf8;

文件存储方式

每个MyISAM在磁盘上存储成3个文件，其文件名都和表名相同，但拓展名分别是 ：

.frm (存储表定义)；

.MYD(MYData , 存储数据)；

.MYI(MYIndex , 存储索引)；

MEMORY ​

​ Memory存储引擎将表的数据存放在内存中。每个MEMORY表实际对应一个磁盘文件，格式是.frm ，该文件中只存储表的结构，而其数据文件，都是存储在内存中，这样有利于数据的快速处理，提高整个表的效率。MEMORY 类型的表访问非常地快，因为他的数据是存放在内存中的，并且默认使用HASH索引 ， 但是服务一旦关闭，表中的数据就会丢失。

MERGE ​

MERGE存储引擎是一组MyISAM表的组合，这些MyISAM表必须结构完全相同，MERGE表本身并没有存储数据，对MERGE类型的表可以进行查询、更新、删除操作，这些操作实际上是对内部的MyISAM表进行的。

​ 对于MERGE类型表的插入操作，是通过INSERT_METHOD子句定义插入的表，可以有3个不同的值，使用FIRST 或 LAST 值使得插入操作被相应地作用在第一或者最后一个表上，不定义这个子句或者定义为NO，表示不能对这个MERGE表执行插入操作。

​ 可以对MERGE表进行DROP操作，但是这个操作只是删除MERGE表的定义，对内部的表是没有任何影响的。

存储引擎的选择 ​

在选择存储引擎时，应该根据应用系统的特点选择合适的存储引擎。对于复杂的应用系统，还可以根据实际情况选择多种存储引擎进行组合。以下是几种常用的存储引擎的使用环境。

  • InnoDB : 是Mysql的默认存储引擎，用于事务处理应用程序，支持外键。如果应用对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询意外，还包含很多的更新、删除操作，那么InnoDB存储引擎是比较合适的选择。InnoDB存储引擎除了有效的降低由于删除和更新导致的锁定， 还可以确保事务的完整提交和回滚，对于类似于计费系统或者财务系统等对数据准确性要求比较高的系统，InnoDB是最合适的选择。
  • MyISAM ： 如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高，那么选择这个存储引擎是非常合适的。
  • MEMORY：将所有数据保存在RAM中，在需要快速定位记录和其他类似数据环境下，可以提供几块的访问。MEMORY的缺陷就是对表的大小有限制，太大的表无法缓存在内存中，其次是要确保表的数据可以恢复，数据库异常终止后表中的数据是可以恢复的。MEMORY表通常用于更新不太频繁的小表，用以快速得到访问结果。
  • MERGE：用于将一系列等同的MyISAM表以逻辑方式组合在一起，并作为一个对象引用他们。MERGE表的优点在于可以突破对单个MyISAM表的大小限制，并且通过将不同的表分布在多个磁盘上，可以有效的改善MERGE表的访问效率。这对于存储诸如数据仓储等VLDB环境十分合适。

1.3 数据类型 ​

数据类型取值范围
TINYINT1字节
SMALLINT2字节
INT4字节 -2^31~2^31-1
BIGINT8字节 -2^63~2^63-1
FLOAT单精度4字节
DOUBLE双精度8字节
DECIMAL没有精度损失
char定长字符串 当存储时，总是是用空格填满右边到指定的长度
varchar变长字符串 存储长度跟文本长度一致
TEXT/CLOB保存文本（字符流） 保存文本
BLOB保存字节（字节流） 可以存储字符和多媒体信息(图片 声音 图像)
date只记录日期 2016-01-02
time只记录时间 11:36:52
year只记录年 2016
datetime记录日期和时间 2016-01-02 11:36:52
timestamp时间戳 2016-01-02 11:36:52
如果timestamps没有给与一个时间数据，那么它会获取系统的时间输入。

1.4 常用函数 ​

  • 字符串函数

    sql
    -- ASCII(str) 返回字符串 str 最左边的那个字符的 ASCII 码值。
select ascii(列名);

-- CONV(N,from_base,to_base) 将数字N实现不同进制间转换，返回字符串。
select conv(11,2,10);

--CONCAT(str1,str2,...) 将参数连接成字符串返回。
select concat(23,23);

--LENGTH(str) 返回一个字符串的长度。

--LOCATE(small,big) 返回一个small在big中的出现位置。
select LOCATE('ab','ddabcdef');

--INSTR(big,small) 返回一个small在big中的出现位置。
select INSTR('ddabcdef'，'ab');

-- IFNULL(参数1,参数2)  判断参数1的值是否为null,如果为null返回参数2的值.

  • 数学函数

    sql
    -- 返回 X 的绝对值：
ABS(X) 
--  取模 (就如 C 中的 % 操作符)。返回 N 被 M 除后的余数： 
MOD(N,M) 
-- 返回不大于 X 的最大整数值： 
FLOOR(X) 
--返回不小于 X 的最小整数： 
CEILING(X) 
--四舍五入
round（X

  • 日期函数

    sql
    -- DATE_ADD(date,INTERVAL expr type)  对日期进行加减运算。 
-- 加一秒
SELECT DATE_ADD("1997-12-31 23:59:59", INTERVAL 1 SECOND); -> 1998-01-01 00:00:00 
-- 加一天
SELECT DATE_ADD("1997-12-31 23:59:59",  INTERVAL 1 DAY); -> 1998-01-01 23:59:59 

--DATE_FORMAT(date,format) 格式化日期函数。
SELECT DATE_FORMAT('1997-10-04 22:23:00', '%H:%i:%s'); -> '22:23:00' 
DATE_FORMAT(‘2008-12-12 02:12:09’,'%y-%m-%d %H:%i:%s‘);
            
-- FROM_UNIXTIME(unix_timestamp [,format]) 将unix下的时间戳变成时间格式。 
-- 以 ‘YYYY-MM-DD HH:MM:SS’ 或 YYYYMMDDHHMMSS 格式返回一个 unix_timestamp 参数值，返回值的形式取决于该函数使用于字符串还是数字语境。 
--如果 format 给出，返回值依 format 字符串被格式。format 可以包含与from_unixtime(dateline);

1.5 索引 ​

MySQL官方对索引的定义为：索引（index）是帮助MySQL高效获取数据的数据结构（有序）。在数据之外，数据库系统还维护者满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据， 这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引

优势

1） 类似于书籍的目录索引，提高数据检索的效率，降低数据库的IO成本。

2） 通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗。

劣势

1） 实际上索引也是一张表，该表中保存了主键与索引字段，并指向实体类的记录，所以索引列也是要占用空间的。

2） 虽然索引大大提高了查询效率，同时却也降低更新表的速度，如对表进行INSERT、UPDATE、DELETE。因为更新表时，MySQL 不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息。

结构 ​

索引是在MySQL的存储引擎层中实现的，而不是在服务器层实现的。所以每种存储引擎的索引都不一定完全相同，也不是所有的存储引擎都支持所有的索引类型的。MySQL目前提供了以下4种索引：

  • BTREE 索引 ： 最常见的索引类型，大部分索引都支持 B 树索引。
  • HASH 索引：只有Memory引擎支持 ， 使用场景简单 。
  • R-tree 索引（空间索引）：空间索引是MyISAM引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少，不做特别介绍。
  • Full-text （全文索引） ：全文索引也是MyISAM的一个特殊索引类型，主要用于全文索引，InnoDB从Mysql5.6版本开始支持全文索引。
索引InnoDB引擎MyISAM引擎Memory引擎
BTREE索引支持支持支持
HASH 索引不支持不支持支持
R-tree 索引不支持支持不支持
Full-text5.6版本之后支持支持不支持

我们平常所说的索引，如果没有特别指明，都是指B+树（多路搜索树，并不一定是二叉的）结构组织的索引。其中聚集索引、复合索引、前缀索引、唯一索引默认都是使用 B+tree 索引，统称为 索引。

索引设计原则 ​

​ 索引的设计可以遵循一些已有的原则，创建索引的时候请尽量考虑符合这些原则，便于提升索引的使用效率，更高效的使用索引。

  • 对查询频次较高，且数据量比较大的表建立索引。

  • 索引字段的选择，最佳候选列应当从where子句的条件中提取，如果where子句中的组合比较多，那么应当挑选最常用、过滤效果最好的列的组合。

  • 使用唯一索引，区分度越高，使用索引的效率越高。

  • 索引可以有效的提升查询数据的效率，但索引数量不是多多益善，索引越多，维护索引的代价自然也就水涨船高。对于插入、更新、删除等DML操作比较频繁的表来说，索引过多，会引入相当高的维护代价，降低DML操作的效率，增加相应操作的时间消耗。另外索引过多的话，MySQL也会犯选择困难病，虽然最终仍然会找到一个可用的索引，但无疑提高了选择的代价。

  • 使用短索引，索引创建之后也是使用硬盘来存储的，因此提升索引访问的I/O效率，也可以提升总体的访问效率。假如构成索引的字段总长度比较短，那么在给定大小的存储块内可以存储更多的索引值，相应的可以有效的提升MySQL访问索引的I/O效率。

  • 利用最左前缀，N个列组合而成的组合索引，那么相当于是创建了N个索引，如果查询时where子句中使用了组成该索引的前几个字段，那么这条查询SQL可以利用组合索引来提升查询效率。

    创建复合索引:

	CREATE INDEX idx_name_email_status ON tb_seller(NAME,email,STATUS);

就相当于
	对name 创建索引 ;
	对name , email 创建了索引 ;
	对name , email, status 创建了索引 ;

1.6 锁 ​

在现实生活中是为我们想要隐藏于外界所使用的一种工具,在计算机中，是协调多个进程或线程并发访问某一资源的

一种机制,在数据库当中，除了传统的计算资源（CPU、RAM、I/O等等）的争用之外，数据也是一种供许多用户共享

访问的资源如何保证数据并发访问的一致性、有效性，是所有数据库必须解决的一个问题

锁分类

按操作分：读锁(共享锁)：针对同一份数据,多个读取操作可以同时进行而不互相影响、写锁(排它锁)：当前写操作没有完成前,会阻断其他写锁和读锁

按粒度分：表锁、行锁、页锁

表锁 ​

偏向MyISAM存储引擎,开销小, 加锁快,无死锁,锁定粒度大,发生锁冲突的概率最高,并发最底,整张表就只能一个人使用

sql
-- 查看表有没有被锁过
show open tables;
-- 对表加锁
lock table user read, user write;
-- unlock tables

读锁

读锁是智能读当前表，不能更新，不能读取其他表。其他连接可以读取当前表，更新需要待读锁接触后更新，可以读其他表。

写锁

对表枷锁后，智能读取自己锁过的表，并且可以修改，但是不能读取别的表。加锁的表不能被其他连接访问，需要等到解锁。

sql
-- 表锁分析
show status like 'table%';
-- Table_locks_immediate:产生表级锁定的次数,表示可以立即获取锁的查询次数
-- Table_locks_waited:出现表级锁定争用而发生等待的次数
-- Myisam的读写锁调度是写优先,这也是myisam不适合做写为主表的引擎。因为写锁后, 其它线程不能做任何操作,大量更新使用，查询很难得到锁, 从而造成永久阻塞

行锁 ​

偏向InnoDB存储引擎,开销大,加锁慢,会出现死锁;锁定粒度最小, 发生锁冲突的概率最底,并发度也最高。

InnoDB与MyISAM的最大不同点:一是支持事务, 二是采用了行级锁。

MYSQL中可以有两种方式进行事务的操作

  1. 手动提交事务

  2. 自动提交事务(5.5以后, 默认 事务会自动提交)

sql
-- 开启事务
start transaction
-- 提交事务
commit
-- 回滚事务
rollback

-- 查看自定提交事务
select @@autocommit; -- 只要是@@开头，表示mysql中的全局变量 1代表了自动开启了提交事物
SHOW VARIABLES LIKE '%commit%';
-- 取消自动提交
SET autocommit = 0; # 1是自动提交事物，0是开启全局事物手动提交（开启事物）

-- 在查询之后添加for update，其它操作会被阻塞,直到锁定的行提交commit;
-- 查看行锁的使用信息：
show status like 'innodb_row_lock%';

执行更新操作（可重复读）

  • 自己可以查看到更新的内容

  • 连接2看不到更新的内容,只有commit后, 才能看到更新的内容

连接1执行更新操作,连接2也执行更新操作,更新同一条记录

  • 连接1没有提交事务时, 连接2更新处于阻塞状态
  • 当commit时, 连接2才会继续执行 连接2更新也要commit

连接1和连接2同时更新数据,但更新的不是同一条记录不会影响

事务原理 ​

  1. 客户端连接数据库服务器，创建连接时创建此用户临时日志文件

  2. 开启事务以后，所有的操作都会先写入到临时日志文件中

  3. 所有的查询操作从表中查询，但会经过日志文件加工后才返回

  4. 如果事务提交commit则将日志文件中的数据写到表中，rollback否则清空日志文件。

事务回滚点 ​

在当前成功的位置设置一个回滚点。可以供后续失败操作返回到该位置，而不是返回所有操作，这个点称之为回滚点。

sql
-- 设置回滚蒂娜
save point 名字
-- 回到回滚点
rollback to 名字

二、基本使用 ​

2.1 基础命令 ​

sh
#链接mysql服务
参数 ： 
	-u, --user=name			指定用户名
	-p, --password[=name]	指定密码
	-h, --host=name			指定服务器IP或域名
	-P, --port=#			指定连接端口

-e, --execute=name		执行SQL语句并退出
# mysql -uroot -p2143 db01 -e "select * from tb_book";


<NolebasePageProperties />




mysql -u 用户名 -p 密码

#退出
exit

# mysqladmin 是一个执行管理操作的客户端程序。可以用它来检查服务器的配置和当前状态、创建并删除数据库等。
 mysqladmin --help  指令查看帮助文档
 
 # mysqlbinlog 日志管理工具
 mysqlbinlog [options]  log-files1 log-files2 ...

选项：
	
	-d, --database=name : 指定数据库名称，只列出指定的数据库相关操作。
	
	-o, --offset=# : 忽略掉日志中的前n行命令。
	
	-r,--result-file=name : 将输出的文本格式日志输出到指定文件。
	
	-s, --short-form : 显示简单格式， 省略掉一些信息。
	
	--start-datatime=date1  --stop-datetime=date2 : 指定日期间隔内的所有日志。
	
	--start-position=pos1 --stop-position=pos2 : 指定位置间隔内的所有日志。

2.2 数据库备份 ​

sh

# mysqldump  用来备份数据库或在不同数据库之间进行数据迁移。备份内容包含创建表，及插入表的SQL语句。
mysqldump [options] db_name [tables]

mysqldump [options] --database/-B db1 [db2 db3...]

mysqldump [options] --all-databases/-A
参数 ： 
	-u, --user=name			指定用户名
	-p, --password[=name]	指定密码
	-h, --host=name			指定服务器IP或域名
	-P, --port=#			指定连接端口
参数：
	--add-drop-database		在每个数据库创建语句前加上 Drop database 语句
	--add-drop-table		在每个表创建语句前加上 Drop table 语句 , 默认开启 ; 不开启 (--skip-add-drop-table)
	
	-n, --no-create-db		不包含数据库的创建语句
	-t, --no-create-info	不包含数据表的创建语句
	-d --no-data			不包含数据
	
	 -T, --tab=name			自动生成两个文件：一个.sql文件，创建表结构的语句；
	 						一个.txt文件，数据文件，相当于select into outfile  
	
#导出整个数据库
mysqldump -u 用户名 -p --default-character-set=utf-8 数据库名 > 导出的文件名(数据库默认编码是latin1) 
# 导出一个表
mysqldump -u 用户名 -p 数据库名 表名> 导出的文件名 
# 导出一个数据库结果 -d 没有数据 –add-drop-table 在每个create语句之前增加一个drop table 
mysqldump -u root -p -d  数据库名 > 导出的文件名


####导入
#1. 登录mysql控制台
mysql -u root -p 
use 数据库 
# 2. 使用source命令，后面参数为脚本文件
source d:demo.sql 

# mysqlimport/source 客户端数据导入工具，用来导入mysqldump 加 -T 参数后导出的文本文件。
mysqlimport [options]  db_name  textfile1  [textfile2...]
# 示例
mysqlimport -uroot -p2143 test /tmp/city.txt
# 如果需要导入sql文件,可以使用mysql中的source 指令 : 
source /root/tb_book.sql

# mysqlshow  客户端对象查找工具，用来很快地查找存在哪些数据库、数据库中的表、表中的列或者索引。

mysqlshow [options] [db_name [table_name [col_name]]]
参数：
--count		显示数据库及表的统计信息（数据库，表 均可以不指定）
-i			显示指定数据库或者指定表的状态信息

#示例
#查询每个数据库的表的数量及表中记录的数量
mysqlshow -uroot -p2143 --count

#查询test库中每个表中的字段书，及行数
mysqlshow -uroot -p2143 test --count

#查询test库中book表的详细情况
mysqlshow -uroot -p2143 test book --count

2.3 忘记密码 ​

sh
* mysql中忘记了root用户的密码？
1. cmd -- > net stop mysql 停止mysql服务
* 需要管理员运行该cmd

2. 使用无验证方式启动mysql服务： mysqld --skip-grant-tables
3. 打开新的cmd窗口,直接输入mysql命令，敲回车。就可以登录成功
4. use mysql;
5. update user set password = password('你的新密码') where user = 'root';
6. 关闭两个窗口
7. 打开任务管理器，手动结束mysqld.exe 的进程
8. 启动mysql服务
9. 使用新密码登录。

2.4 修改编码 ​

sh
# 关闭mysql服务
sudo service mysql stop
# 修改my.cnf配置文件
character_set_server = utf8
init_connect='SET NAMES utf8'

# 查看编码
show variables like '%char%';

三、核心知识 ​

3.1 Sql语句 ​

参照数据库相关.md文档中的sql语句

3.2 存储过程 ​

MySQL从5.0版本开始支持存储过程和函数。

存储过程和函数是事先经过编译和存储在数据库中的一段SQL语句的集合

存储过程和函数的区别在于函数必须有返回值，而存储过程没有，存储过程的参数可以使用IN，OUT，INOUT类

型，而函数的参数只能是IN类型的，

创建，删除，修改存储过程或者函数都需要权限，例如创建存储过程或者函数需要CREATE ROUNTINE权限，修改或

者删除存储过程或者函数需要ALTER ROUTINE权限，执行存储过程或者函数需要EXECUTE权限。

存储过程命令 ​

sql
-- 创建存储过程 
DELIMITER $$ 
CREATE PROCEDURE testa() 
BEGIN 
	SELECT * FROM student WHERE id=2; 
END $$ 
-- 调用存储过程 
call testa();

-- 删除存储过程 
DROP PROCEDURE testa1; 
-- 删除函数 
DROP FUNCTION testa1;

-- 查看存储过程或者函数的状态 
SHOW PROCEDURE STATUS LIKE 'testa'; 
-- 查看存储过程或者函数的定义 
SHOW CREATE PROCEDURE testa；

变量 ​

sql
-- 变量的声明使用declare,一句declare只声明一个变量，变量必须先声明后使用
-- 变量具有数据类型和长度，与mysql的SQL数据类型保持一致，因此甚至还能指定默认值、字符集和排序规则等
-- 变量可以通过set来赋值，也可以通过select into的方式赋值
-- 变量需要返回，可以使用select语句,如：select 变量名
-- 作用范围在begin与end块之间，end结束变量的作用范围即结束
-- 需要多个块之间传递值，可以使用全局变量，即放在所有代码块之前。
-- 传参变量是全局的，可以在多个块之间起作用
BEGIN 
	DECLARE my_uname VARCHAR(32) DEFAULT ''; 
	SET my_uname='itheima'; 
	SELECT NAME INTO my_uname FROM student WHERE id=2; 
	SELECT my_uname; 
END $$

参数 ​

sql
-- IN
-- 传入参数：类型为IN,表示该参数的值必须在调用存储过程时指定，如果不显式指定为IN，那么默认就是IN类型
-- IN类型参数一般只用于传入，在调用存储过程中一般不作修改和返回
-- OUT
-- OUT是传出参数：在调用存储过程中，可以改变其值，并可返回，不能用于传入参数值
-- 调用存储过程时，OUT参数也需要指定，但必须是变量，不能是常量
-- INOUT
-- 可变变量INOUT：调用时可传入值，在调用过程中，可修改其值，同时也可返回值。
-- 集合了IN和OUT类型的参数功能
-- 调用时传入的是变量，而不是常量
DELIMITER $$ 
CREATE PROCEDURE getName22(IN my_uid INT,OUT my_uname VARCHAR(32)) 
BEGIN 
	SELECT NAME INTO my_uname FROM student WHERE id=my_uid; 
	SELECT my_uname; 
END; $$

SET @uname:=''; 
CALL getName22(2,@uname); 
SELECT @uname AS myName;

逻辑语句 ​

sql
-- 条件 if
IF(my_uid%2=0) 
THEN 
	SELECT NAME INTO my_uname FROM student WHERE id=my_uid;
	SELECT my_uname; 
ELSE
	SELECT my_uid; 
END IF;

-- while循环
DECLARE i INT DEFAULT 0; 
WHILE(i< 10) DO 
    BEGIN
        SELECT i; SET i=i+1; 
        INSERT INTO users(NAME , address) VALUES("孙悟空" , "广州"); 
    END ; 
END WHILE;

-- repeat循环
DECLARE i INT DEFAULT 100; 
REPEAT 
    BEGIN
        SELECT i; SET i=i+1; 
        INSERT INTO users(NAME) VALUES('黑马'); 
    END ; 
UNTIL i >= 110
END REPEAT;

游标 ​

在存储过程和函数中，可以使用光标（有时也称为游标）对结果集进行循环的处理

sql
-- 编写存储过程，使用光标，把id为偶数的记录逐一更新用户名。 
DELIMITER $$ 
CREATE PROCEDURE testcursor() 
BEGIN 
	-- 变量，条件，处理程序，光标，都是通过DECLARE定义的，它们之间是有先后顺序要求的，变量和条件必须在最 前面声明，然后才能是光标的申明，最后才可以是处理程序的申明。
    DECLARE stopflag INT DEFAULT 0; 
    DECLARE my_uname VARCHAR(20); 
    DECLARE uname_cur CURSOR FOR SELECT NAME FROM student WHERE id%2=0 ; 
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET stopflag=1; 
    OPEN uname_cur; -- 打开游标 
    FETCH uname_cur INTO my_uname; -- 游标向前走一步，取出一条记录放到变量my_uname中。 
    WHILE( stopflag=0 ) DO -- 如果游标还没有到结尾，就继续 
    BEGIN
        UPDATE student SET NAME=CONCAT(my_uname,'_cur') WHERE NAME=my_uname; 
        FETCH uname_cur INTO my_uname; END ; 
    END WHILE; 
    CLOSE uname_cur; 
END; 
$$
DELIMITER ;

触发器 ​

Mysql从5.0.2开始支持触发器功能，触发器是与表有关的数据库对象，在满足定义条件时触发，并执行触发器中定义

的语句集合、触发器这种特性可以协助应用在数据库端确定数据的完整性。

sql
DELIMITER $$ 
-- 创建触发器，触发时机为after/before 
-- insert on 操作事件包括insert,UPDATE,DELETE
-- users 是触发的表
-- FOR EACH ROW  是触发范围
CREATE TRIGGER tr_users_insert AFTER INSERT ON users 
FOR EACH ROW 
BEGIN 
	INSERT INTO oplog(uid,uname,ACTION,optime) VALUES(NEW.uid,NEW.uname,'insert',NOW()); 
END; $$

-- 删除触发器
drop trigger [schema_name.]trigger_name

-- 查看触发器
show triggers

事件调度器 ​

事件调度器是MySQL中提供的可做定时操作处理，或者周期操作处理的一个对象。

sql
-- 先确认是否开启了事件调度的支持
show variables like '%event_scheduler%'; 
set global event_scheduler =on;

DELIMITER $$ 
-- 创建事件调度器
CREATE EVENT IF NOT EXISTS event_hello 
-- 执行时间和事件间隔 每三秒执行一次
-- On schedule at ‘2016-12-12 04:00:00’  在特定时间执行一次
-- on schedule every 1 day starts ‘2016-12-12 20:20:20’ 每天在20：20：20执行一次
ON SCHEDULE EVERY 3 SECOND 
-- 调度计划执行完成后是否还保留
ON COMPLETION PRESERVE 
DO 
    BEGIN
    	INSERT INTO users(NAME , address) VALUES('王五','广州'); 
    END$$ 
DELIMITER ;

3.3 试图 ​

视图（View）是一种虚拟存在的表。视图并不在数据库中实际存在，行和列数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的。通俗的讲，视图就是一条SELECT语句执行后返回的结果集。所以我们在创建视图的时候，主要的工作就落在创建这条SQL查询语句上。

视图相对于普通的表的优势主要包括以下几项。

  • 简单：使用视图的用户完全不需要关心后面对应的表的结构、关联条件和筛选条件，对用户来说已经是过滤好的复合条件的结果集。
  • 安全：使用视图的用户只能访问他们被允许查询的结果集，对表的权限管理并不能限制到某个行某个列，但是通过视图就可以简单的实现。
  • 数据独立：一旦视图的结构确定了，可以屏蔽表结构变化对用户的影响，源表增加列对视图没有影响；源表修改列名，则可以通过修改视图来解决，不会造成对访问者的影响。

创建视图的语法为：

sql
CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]

VIEW view_name [(column_list)]

AS select_statement

[WITH [CASCADED | LOCAL] CHECK OPTION]

修改视图的语法为：

sql
ALTER [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]

VIEW view_name [(column_list)]

AS select_statement

[WITH [CASCADED | LOCAL] CHECK OPTION]

选项 : 
	WITH [CASCADED | LOCAL] CHECK OPTION 决定了是否允许更新数据使记录不再满足视图的条件。
	
	LOCAL ： 只要满足本视图的条件就可以更新。
	CASCADED ： 必须满足所有针对该视图的所有视图的条件才可以更新。 默认值.

删除视图

语法 :

sql
DROP VIEW [IF EXISTS] view_name [, view_name] ...[RESTRICT | CASCADE]

示例 , 删除视图city_country_view :

sql
DROP VIEW city_country_view ;

3.4 MySQL 日志 ​

错误日志 ​

错误日志是 MySQL 中最重要的日志之一，它记录了当 mysqld 启动和停止时，以及服务器在运行过程中发生任何严重错误时的相关信息。当数据库出现任何故障导致无法正常使用时，可以首先查看此日志。

该日志是默认开启的 ， 默认存放目录为 mysql 的数据目录（var/lib/mysql）, 默认的日志文件名为 hostname.err（hostname是主机名）。

查看日志位置指令 ：

sql
show variables like 'log_error%';

二进制日志 ​

二进制日志（BINLOG）记录了所有的 DDL（数据定义语言）语句和 DML（数据操纵语言）语句，但是不包括数据查询语句。此日志对于灾难时的数据恢复起着极其重要的作用，MySQL的主从复制， 就是通过该binlog实现的。

二进制日志，默认情况下是没有开启的，需要到MySQL的配置文件中开启，并配置MySQL日志的格式。

配置文件位置 : /usr/my.cnf

日志存放位置 : 配置时，给定了文件名但是没有指定路径，日志默认写入Mysql的数据目录。

#配置开启binlog日志， 日志的文件前缀为 mysqlbin -----> 生成的文件名如 : mysqlbin.000001,mysqlbin.000002
log_bin=mysqlbin

#配置二进制日志的格式
binlog_format=STATEMENT
日志格式 ​

STATEMENT

该日志格式在日志文件中记录的都是SQL语句（statement），每一条对数据进行修改的SQL都会记录在日志文件中，通过Mysql提供的mysqlbinlog工具，可以清晰的查看到每条语句的文本。主从复制的时候，从库（slave）会将日志解析为原文本，并在从库重新执行一次。

ROW

该日志格式在日志文件中记录的是每一行的数据变更，而不是记录SQL语句。比如，执行SQL语句 ： update tb_book set status='1' , 如果是STATEMENT 日志格式，在日志中会记录一行SQL文件； 如果是ROW，由于是对全表进行更新，也就是每一行记录都会发生变更，ROW 格式的日志中会记录每一行的数据变更。

MIXED

这是目前MySQL默认的日志格式，即混合了STATEMENT 和 ROW两种格式。默认情况下采用STATEMENT，但是在一些特殊情况下采用ROW来进行记录。MIXED 格式能尽量利用两种模式的优点，而避开他们的缺点。

日志读取 ​

由于日志以二进制方式存储，不能直接读取，需要用mysqlbinlog工具来查看，语法如下 ：

mysqlbinlog log-file；

查看STATEMENT格式日志

执行插入语句 ：

SQL
insert into tb_book values(null,'Lucene','2088-05-01','0');

查看日志文件 ：mysqlbin.index : 该文件是日志索引文件 ， 记录日志的文件名； mysqlbing.000001 ：日志文件

查看日志内容 ：

mysqlbinlog mysqlbing.000001；

查看ROW格式日志

配置 :

#配置开启binlog日志， 日志的文件前缀为 mysqlbin -----> 生成的文件名如 : mysqlbin.000001,mysqlbin.000002
log_bin=mysqlbin

#配置二进制日志的格式
binlog_format=ROW

插入数据 :

sql
insert into tb_book values(null,'SpringCloud实战','2088-05-05','0');

如果日志格式是 ROW , 直接查看数据 , 是查看不懂的 ; 可以在mysqlbinlog 后面加上参数 -vv

SQL
mysqlbinlog -vv mysqlbin.000002
日志删除 ​

对于比较繁忙的系统，由于每天生成日志量大 ，这些日志如果长时间不清楚，将会占用大量的磁盘空间。下面我们将会讲解几种删除日志的常见方法 ：

方式一

通过 Reset Master 指令删除全部 binlog 日志，删除之后，日志编号，将从 xxxx.000001重新开始 。

执行删除日志指令：

Reset Master

方式二

执行指令 purge master logs to 'mysqlbin.******' ，该命令将删除 ****** 编号之前的所有日志。

方式三

执行指令 purge master logs before 'yyyy-mm-dd hh24:mi:ss' ，该命令将删除日志为 "yyyy-mm-dd hh24:mi:ss" 之前产生的所有日志 。

方式四

设置参数 --expire_logs_days=# ，此参数的含义是设置日志的过期天数， 过了指定的天数后日志将会被自动删除，这样将有利于减少DBA 管理日志的工作量。

配置：

log_bin=mysqlbin
binlog_format=ROW
--expire_logs_days=3

查询日志 ​

查询日志中记录了客户端的所有操作语句，而二进制日志不包含查询数据的SQL语句。

默认情况下， 查询日志是未开启的。如果需要开启查询日志，可以在 mysql 的配置文件 /usr/my.cnf 中配置中设置以下配置 ：

#该选项用来开启查询日志 ， 可选值 ： 0 或者 1 ； 0 代表关闭， 1 代表开启 
general_log=1

#设置日志的文件名 ， 如果没有指定， 默认的文件名为 host_name.log 
general_log_file=file_name

慢查询日志 ​

慢查询日志记录了所有执行时间超过参数 long_query_time 设置值并且扫描记录数不小于 min_examined_row_limit 的所有的SQL语句的日志。long_query_time 默认为 10 秒，最小为 0， 精度可以到微秒。

慢查询日志默认是关闭的 。可以通过两个参数来控制慢查询日志 ：

# 该参数用来控制慢查询日志是否开启， 可取值： 1 和 0 ， 1 代表开启， 0 代表关闭
slow_query_log=1 

# 该参数用来指定慢查询日志的文件名
slow_query_log_file=slow_query.log

# 该选项用来配置查询的时间限制， 超过这个时间将认为值慢查询， 将需要进行日志记录， 默认10s
long_query_time=10

和错误日志、查询日志一样，慢查询日志记录的格式也是纯文本，可以被直接读取。

可以借助于mysql自带的 mysqldumpslow 工具， 来对慢查询日志进行分类汇总

mysqldumpslow 文件名

3.5 sql执行顺序 ​

编写顺序

SQL
SELECT DISTINCT
	<select list>
FROM
	<left_table> <join_type>
JOIN
	<right_table> ON <join_condition>
WHERE
	<where_condition>
GROUP BY
	<group_by_list>
HAVING
	<having_condition>
ORDER BY
	<order_by_condition>
LIMIT
	<limit_params>

执行顺序

sql
FROM	<left_table>

ON 		<join_condition>

<join_type>		JOIN	<right_table>

WHERE		<where_condition>

GROUP BY 	<group_by_list>

HAVING		<having_condition>

SELECT DISTINCT		<select list>

ORDER BY	<order_by_condition>

LIMIT		<limit_params>

3.6 正则表达式使用 ​

正则表达式（Regular Expression）是指一个用来描述或者匹配一系列符合某个句法规则的字符串的单个字符串。

符号含义
^在字符串开始处进行匹配
$在字符串末尾处进行匹配
.匹配任意单个字符, 包括换行符
[...]匹配出括号内的任意字符
[^...]匹配不出括号内的任意字符
a*匹配零个或者多个a(包括空串)
a+匹配一个或者多个a(不包括空串)
a?匹配零个或者一个a
a1|a2匹配a1或a2
a(m)匹配m个a
a(m,)至少匹配m个a
a(m,n)匹配m个a 到 n个a
a(,n)匹配0到n个a
(...)将模式元素组成单一元素
select * from emp where name regexp '^T';

select * from emp where name regexp '2$';

select * from emp where name regexp '[uvw]';

3.7 锁 ​

锁是计算机协调多个进程或线程并发访问某一资源的机制（避免争抢）。

在数据库中，除传统的计算资源（如 CPU、RAM、I/O 等）的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂。

锁分类 ​

从对数据操作的粒度分 ：

1） 表锁：操作时，会锁定整个表。

2） 行锁：操作时，会锁定当前操作行。

从对数据操作的类型分：

1） 读锁（共享锁）：针对同一份数据，多个读操作可以同时进行而不会互相影响。

2） 写锁（排它锁）：当前操作没有完成之前，它会阻断其他写锁和读锁。

Mysql锁 ​

MySQL的锁机制比较简单，其最显著的特点是不同的存储引擎支持不同的锁机制。下表中罗列出了各存储引擎对锁的支持情况：

存储引擎表级锁行级锁页面锁
MyISAM支持不支持不支持
InnoDB支持支持不支持
MEMORY支持不支持不支持
BDB支持不支持支持

MySQL这3种锁的特性可大致归纳如下 ：

锁类型特点
表级锁偏向MyISAM 存储引擎，开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高,并发度最低。
行级锁偏向InnoDB 存储引擎，开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低,并发度也最高。
页面锁开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用，如Web 应用；而行级锁则更适合于有大量按索引条件并发更新少量不同数据，同时又有并查询的应用，如一些在线事务处理（OLTP）系统。

MyISAM 表锁 ​

MyISAM 存储引擎只支持表锁，这也是MySQL开始几个版本中唯一支持的锁类型。

MyISAM 在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁，在执行更新操作（UPDATE、DELETE、INSERT 等）前，会自动给涉及的表加写锁，这个过程并不需要用户干预，因此，用户一般不需要直接用 LOCK TABLE 命令给 MyISAM 表显式加锁。

显示加表锁语法：

SQL
加读锁 ： lock table table_name read;

加写锁 ： lock table table_name write；

​ 1） 对MyISAM 表的读操作，不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求；

​ 2） 对MyISAM 表的写操作，则会阻塞其他用户对同一表的读和写操作；

​ 简而言之，就是读锁会阻塞写，但是不会阻塞读。而写锁，则既会阻塞读，又会阻塞写。

查看锁的争用情况 ​
show open tables；

In_user : 表当前被查询使用的次数。如果该数为零，则表是打开的，但是当前没有被使用。

Name_locked：表名称是否被锁定。名称锁定用于取消表或对表进行重命名等操作。

show status like 'Table_locks%';

Table_locks_immediate ： 指的是能够立即获得表级锁的次数，每立即获取锁，值加1。

Table_locks_waited ： 指的是不能立即获取表级锁而需要等待的次数，每等待一次，该值加1，此值高说明存在着较为严重的表级锁争用情况。

InnoDB 行锁 ​

行锁特点 ：偏向InnoDB 存储引擎，开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低,并发度也最高。

InnoDB 与 MyISAM 的最大不同有两点：一是支持事务；二是 采用了行级锁。

Mysql 的数据库的默认隔离级别为 Repeatable read ， 查看方式：

show variables like 'tx_isolation';
行锁模式 ​

InnoDB 实现了以下两种类型的行锁。

  • 共享锁（S）：又称为读锁，简称S锁，共享锁就是多个事务对于同一数据可以共享一把锁，都能访问到数据，但是只能读不能修改。
  • 排他锁（X）：又称为写锁，简称X锁，排他锁就是不能与其他锁并存，如一个事务获取了一个数据行的排他锁，其他事务就不能再获取该行的其他锁，包括共享锁和排他锁，但是获取排他锁的事务是可以对数据就行读取和修改。

对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁（X)；

对于普通SELECT语句，InnoDB不会加任何锁；

可以通过以下语句显示给记录集加共享锁或排他锁 。

共享锁（S）：SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE

排他锁（X) ：SELECT * FROM table_name WHERE ... FOR UPDATE
无索引行锁升级为表锁 ​

如果不通过索引条件检索数据，那么InnoDB将对表中的所有记录加锁，实际效果跟表锁一样。

查看当前表的索引 ： show index from test_innodb_lock ;

间隙锁危害 ​

当我们用范围条件，而不是使用相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据进行加锁； 对于键值在条件范围内但并不存在的记录，叫做 "间隙（GAP）" ， InnoDB也会对这个 "间隙" 加锁，这种锁机制就是所谓的 间隙锁（Next-Key锁） 。

InnoDB 行锁争用情况 ​
sql
show  status like 'innodb_row_lock%';
Innodb_row_lock_current_waits: 当前正在等待锁定的数量

Innodb_row_lock_time: 从系统启动到现在锁定总时间长度

Innodb_row_lock_time_avg:每次等待所花平均时长

Innodb_row_lock_time_max:从系统启动到现在等待最长的一次所花的时间

Innodb_row_lock_waits: 系统启动后到现在总共等待的次数


当等待的次数很高，而且每次等待的时长也不小的时候，我们就需要分析系统中为什么会有如此多的等待，然后根据分析结果着手制定优化计划。

InnoDB存储引擎由于实现了行级锁定，虽然在锁定机制的实现方面带来了性能损耗可能比表锁会更高一些，但是在整体并发处理能力方面要远远由于MyISAM的表锁的。当系统并发量较高的时候，InnoDB的整体性能和MyISAM相比就会有比较明显的优势。

但是，InnoDB的行级锁同样也有其脆弱的一面，当我们使用不当的时候，可能会让InnoDB的整体性能表现不仅不能比MyISAM高，甚至可能会更差。

优化建议：

  • 尽可能让所有数据检索都能通过索引来完成，避免无索引行锁升级为表锁。
  • 合理设计索引，尽量缩小锁的范围
  • 尽可能减少索引条件，及索引范围，避免间隙锁
  • 尽量控制事务大小，减少锁定资源量和时间长度
  • 尽可使用低级别事务隔离（但是需要业务层面满足需求）

四、SQL优化 ​

4.1 sql执行分析 ​

查看sql执行频率 ​

MySQL 客户端连接成功后，通过 show [session|global] status 命令可以提供服务器状态信息。show [session|global] status 可以根据需要加上参数“session”或者“global”来显示 session 级（当前连接）的计结果和 global 级（自数据库上次启动至今）的统计结果。如果不写，默认使用参数是“session”。

下面的命令显示了当前 session 中所有统计参数的值：

show status like 'Com_______';
show status like 'Innodb_rows_%';

Com_xxx 表示每个 xxx 语句执行的次数，我们通常比较关心的是以下几个统计参数。

参数含义
Com_select执行 select 操作的次数，一次查询只累加 1。
Com_insert执行 INSERT 操作的次数，对于批量插入的 INSERT 操作，只累加一次。
Com_update执行 UPDATE 操作的次数。
Com_delete执行 DELETE 操作的次数。
Innodb_rows_readselect 查询返回的行数。
Innodb_rows_inserted执行 INSERT 操作插入的行数。
Innodb_rows_updated执行 UPDATE 操作更新的行数。
Innodb_rows_deleted执行 DELETE 操作删除的行数。
Connections试图连接 MySQL 服务器的次数。
Uptime服务器工作时间。
Slow_queries慢查询的次数。

Com_*** : 这些参数对于所有存储引擎的表操作都会进行累计。

Innodb_*** : 这几个参数只是针对InnoDB 存储引擎的，累加的算法也略有不同。

定位低效率执行SQL ​

可以通过以下两种方式定位执行效率较低的 SQL 语句。

  • 慢查询日志 : 通过慢查询日志定位那些执行效率较低的 SQL 语句，用--log-slow-queries[=file_name]选项启动时，mysqld 写一个包含所有执行时间超过 long_query_time 秒的 SQL 语句的日志文件。具体可以查看本书第 26 章中日志管理的相关部分。

  • show processlist : 慢查询日志在查询结束以后才纪录，所以在应用反映执行效率出现问题的时候查询慢查询日志并不能定位问题，可以使用show processlist命令查看当前MySQL在进行的线程，包括线程的状态、是否锁表等，可以实时地查看 SQL 的执行情况，同时对一些锁表操作进行优化。

    1） id列，用户登录mysql时，系统分配的"connection_id"，可以使用函数connection_id()查看

2） user列，显示当前用户。如果不是root，这个命令就只显示用户权限范围的sql语句

3） host列，显示这个语句是从哪个ip的哪个端口上发的，可以用来跟踪出现问题语句的用户

4） db列，显示这个进程目前连接的是哪个数据库

5） command列，显示当前连接的执行的命令，一般取值为休眠（sleep），查询（query），连接（connect）等

6） time列，显示这个状态持续的时间，单位是秒

7） state列，显示使用当前连接的sql语句的状态，很重要的列。state描述的是语句执行中的某一个状态。一个sql语句，以查询为例，可能需要经过copying to tmp table、sorting result、sending data等状态才可以完成

8） info列，显示这个sql语句，是判断问题语句的一个重要依据

explain分析执行计划 ​

通过 EXPLAIN或者 DESC命令获取 MySQL如何执行 SELECT 语句的信息，包括在 SELECT 语句执行过程中表如何连接和连接的顺序。

查询SQL语句的执行计划 ：

sql
explain  select * from tb_item where id = 1;
字段含义
idselect查询的序列号，是一组数字，表示的是查询中执行select子句或者是操作表的顺序。
select_type表示 SELECT 的类型，常见的取值有 SIMPLE（简单表，即不使用表连接或者子查询）、PRIMARY（主查询，即外层的查询）、UNION（UNION 中的第二个或者后面的查询语句）、SUBQUERY（子查询中的第一个 SELECT）等
table输出结果集的表
type表示表的连接类型，性能由好到差的连接类型为( system ---> const -----> eq_ref ------> ref -------> ref_or_null----> index_merge ---> index_subquery -----> range -----> index ------> all ) 一般来说， 我们需要保证查询至少达到 range 级别， 最好达到ref 。
possible_keys表示查询时，可能使用的索引
key表示实际使用的索引
key_len表示索引中使用的字节数， 该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下， 长度越短越好 。
rows扫描行的数量
extra执行情况的说明和描述
extra含义
using filesort说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取， 称为 “文件排序”, 效率低。
using temporary使用了临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于 order by 和 group by； 效率低
using index表示相应的select操作使用了覆盖索引， 避免访问表的数据行， 效率不错。

show profile分析SQL ​

Mysql从5.0.37版本开始增加了对 show profiles 和 show profile 语句的支持。show profiles 能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。

通过 have_profiling 参数，能够看到当前MySQL是否支持profile：

select @@have_profiling

默认profiling是关闭的，可以通过set语句在Session级别开启profiling：

set profiling=1; //开启profiling 开关；

show profiles 显示查询的sql的执行耗时后，可通过show profile for query query_id 查看到该SQL执行过程中每个线程的状态和消耗的时间：

在获取到最消耗时间的线程状态后，MySQL支持进一步选择all、cpu、block io 、context switch、page faults等明细类型类查看MySQL在使用什么资源上耗费了过高的时间。例如，选择查看CPU的耗费时间 ：

字段含义
Statussql 语句执行的状态
Durationsql 执行过程中每一个步骤的耗时
CPU_user当前用户占有的cpu
CPU_system系统占有的cpu

trace分析优化器执行计划 ​

MySQL5.6提供了对SQL的跟踪trace, 通过trace文件能够进一步了解为什么优化器选择A计划, 而不是选择B计划。

打开trace ， 设置格式为 JSON，并设置trace最大能够使用的内存大小，避免解析过程中因为默认内存过小而不能够完整展示。

sql
SET optimizer_trace="enabled=on",end_markers_in_json=on;
set optimizer_trace_max_mem_size=1000000;

执行SQL语句 ：

sql
select * from tb_item where id < 4;

最后， 检查information_schema.optimizer_trace就可以知道MySQL是如何执行SQL的 ：

sql
select * from information_schema.optimizer_trace\G;

4.2 慢查询日志 ​

慢查询日志：记录具体执行效率较低的SQL语句的日志信息。

在默认情况下mysql慢查询日志记录是关闭的，同时慢查询日志默认不记录：管理语句和不使用索引进行查询的语句。

MySQL的慢查询日志是MySQL提供的一种日志记录,它用来记录在MySQL中响应时间超过阀值的语句 具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。long_query_time的默认值为10S

sql
show variables like '%slow_query_log%' 
开启set global slow_query_log=1; 
-- 只对当前数据库生效, 如果重启后, 则会失效 如果想永久生效,必须要修改配置文件 
slow_query_log = 1 
slow_query_log_file=地址
-- 慢查询时间
show variables like 'long_query_time' 
set global long_query_time=4;

show global variables like 'long_query_time'; 
select sleep(4) show global status like '%slow_queries%';

慢查询日志分析工具mysqldumpslow ​

MySQL默认安装了mysqldumpslow工具实现对慢查询日志信息的分析。

sql
#-s 按照那种方式排序 
#c：访问计数 
#l：锁定时间 
#r:返回记录 
#al：平均锁定时间 
#ar：平均访问记录数 
#at：平均查询时间 
#-t 是top n的意思，返回多少条数据。 
#-g 可以跟上正则匹配模式，大小写不敏感
-- 得到返回记录集最多的10个SQL。 
mysqldumpslow.pl -s r -t 10 C:\soft\DESKTOP-8GVEK4U-slow.log 
-- 得到访问次数最多的10个SQL 
mysqldumpslow.pl -s c -t 10 C:\soft\DESKTOP-8GVEK4U-slow.log 
-- 得到按照时间排序的前10条里面含有左连接的查询语句。 
mysqldumpslow.pl -s t -t 10 -g “left join” C:\soft\DESKTOP-8GVEK4U-slow.log 
-- 另外建议在使用这些命令时结合 | 和more 使用 ，否则有可能出现刷屏的情况。 
mysqldumpslow.pl -s r -t 20 C:\soft\DESKTOP-8GVEK4U-slow.log
Count: 4（执行了多少次） Time=375.01s（每次执行的时间） (1500s)（一共执行了多少时间） Lock=0.00s (0s)（等待锁的时间） Rows=10200.3（每次返回的记录数） (40801)（总共返回的记录 数）, username[password]@[10.194.172.41]

Show Profifile分析 ​

Show Profifile是mysql提供的可以用来分析当前会话中sql语句执行的资源消耗情况的工具，可用于sql调优的测量。默认情况下处于关闭状态，并保存最近15次的运行结果。把一条sql在mysql当中每一个环节耗费的时候都记录下来。

sql
-- 1.查看当前版本是否支持 
Show variables like 'profiling'; 
-- 2.打开profile 
set profiling = on 
-- 3.查看结果 
show profiles 
-- 4.诊断sql 
show profile cpu,block io for query 88;

当出现以下选项时,要进行优化

Creating tmp table 创建临时表copy数据到临时表,用完再进行删除

Copying to tmp table on disk 把内存中临时表复制到磁盘

Locked 被锁定

EXPLAIN分析低效SQL的执行计划 ​

查询执行计划，使用explain关键字,可以模拟优化器执行的SQL语句，从而知道MYSQL是如何处理sql语句的 通过Explain可以分析查询语句或表结构的性能瓶颈。

sql
EXPLAIN sql

分析包含信息

  • id：select查询的序列号包含一组数字,表示查询中执行select子句或操作表的顺序。相同,顺序走；不同,看谁大,大的先执行。

  • type 访问类型排列,从上到下是最好的方式到最差的方式，一般查询保证查询至少达到range级别，最好能达到ref

    • system：表中有一行记录(系统表) 这是const类型的特例 , 平时不会出现

    • const：表示通过索引一次就找到了，const用于比较primary 或者 unique索引. 直接查询主键或者唯一索引。因为只匹配一行数据,所以很快

    • eq_ref：唯一性索引扫描，对于每个索引键,表中只有一条记录与之匹配。常见于主键或唯一索引扫描

    • ref：非唯一性索引扫描,返回匹配某个单独值的所有行。本质上也是一种索引访问。它返回所有匹配某个单独值的行，可能会找到多个符合条件的行,所以它应该属于查找和扫描的混合体

    • range：只检索给定范围的行,使用一个索引来选择行。key列显示使用了哪个索引，一般就是在你的where语句中出现between\ in等查询。这种范围扫描索引比全表扫描要好，因为它只需要开始于索引的某一点.而结束语另一点，不用扫描全部索引

    • index：Full Index Scan，index与All区别为index类型只遍历索引树,通常比All要快,因为索引文件通常比数据文件要小。all和index都是读全表,但index是从索引中读取,all是从硬盘当中读取

    • ALL：将全表进行扫描,从硬盘当中读取数据如果出现了All 数据量非常大, 一定要去做优化

  • key：实际使用的索引,如果为NULL,则没有使用索引，查询中若使用了覆盖索引 ,则该索引仅出现在key列表中。possible_keys与key关系 理论应该用到哪些索引 实际用到了哪些索引覆盖索引。查询的字段和建立的字段刚好吻合,这种我们称为覆盖索引

  • select_type:查询类型,主要用于区别普通查询,联合查询,子查询等复杂查询

    • SIMPLE 简单select查询,查询中不包含子查询或者UNION
    • PRIMARY查询中若包含任何复杂的子查询,最外层查询则被标记为primary
    • SUBQUERY：在select或where中包含了子查询
    • DERIVED：在from列表中包含的子查询被标记为derived(衍生)把结果放在临时表当中
    • UNION 若第二个select出现的union之后,则被标记为union 若union包含在from子句的子查询中,外层select将被标记为deriver
    • UNION RESULT从union表获取结果select，两个UNION合并的结果集在最后

  • table 显示这一行的数据是关于哪张表的

  • partitions 如果查询是基于分区表的话, 会显示查询访问的分区

  • possible_keys： key与keys主要作用,是查看是否使用了建立的索引, 也即判断索引失效 在建立多个索引 的情况下,mysql最终用到了哪一个索引 possible_keys 显示可能应用在这张表中的索引,一个或者多个 查询涉及到的字段上若存在索引,则该索引将被列出,但不一定被查询实际使用 可能自己创建了4个索引,在执行的时候,可能根据内部的自动判断,只使用了3个

  • key_len：表示索引中使用的字节数,可通过该列计算查询中使用的索引长度

  • ref 索引是否被引入到, 到底引用到了哪几个索引

  • rows根据表统计信息及索引选用情况,大致估算出找到所需的记录所需要读取的行数

  • fifiltered:满足查询的记录数量的比例，注意是百分比，不是具体记录数, 值越大越好，fifiltered列的值依赖统计信息，并不十分准确

  • Extra:产生的值

    • Using fifilesort说明mysql会对数据使用一个外部的索引排序,而不是按照表内的索引顺序进行Mysql中无法利用索引完成排序操作称为"文件排序"

    • Using temporary使用了临时表保存中间结果,Mysql在对查询结果排序时, 使用了临时表,常见于排序orderby 和分组查询group by

    • use index表示相应的select中使用了覆盖索引,避免访问了表的数据行, 效率很好如果同时出现using where 表明索引被用来执行索引键值的查找如果没有同时出现using where 表明索引 用来读取数据而非执行查找动作

    • using where: 表明使用了wher过滤

    • using join buffffer: 使用了连接缓存

    • impossible where: where 子句的值总是false 不能用来获取任何元组

      using index ：使用覆盖索引的时候就会出现

      using where：在查找使用索引的情况下，需要回表去查询所需的数据

      using index condition：查找使用了索引，但是需要回表查询数据

      using index ; using where：查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据

4.3 索引使用 ​

避免索引失效 ​

1). 全值匹配 ，对索引中所有列都指定具体值。

2). 最左前缀法则

如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始，并且不跳过索引中的列。

3). 范围查询右边的列，不能使用索引

4). 不要在索引列上进行运算操作， 索引将失效。

5). 字符串不加单引号，造成索引失效。

6). 尽量使用覆盖索引，避免select *

尽量使用覆盖索引（只访问索引的查询（索引列完全包含查询列）），减少select * 。

7). 用or分割开的条件， 如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到

8). 以%开头的Like模糊查询，索引失效。

如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效。

9). 如果MySQL评估使用索引比全表更慢，则不使用索引。

10). is NULL ， is NOT NULL 索引失效。

11). in 走索引， not in 索引失效。

12). 单列索引和复合索引。

尽量使用复合索引，而少使用单列索引 。

创建复合索引

create index idx_name_sta_address on tb_seller(name, status, address);

就相当于创建了三个索引 ： 
	name
	name + status
	name + status + address

创建单列索引

create index idx_seller_name on tb_seller(name);
create index idx_seller_status on tb_seller(status);
create index idx_seller_address on tb_seller(address);

数据库会选择一个最优的索引（辨识度最高索引）来使用，并不会使用全部索引 。

查看索引使用情况 ​

sql
show status like 'Handler_read%';	

show global status like 'Handler_read%';
Handler_read_first：索引中第一条被读的次数。如果较高，表示服务器正执行大量全索引扫描（这个值越低越好）。

Handler_read_key：如果索引正在工作，这个值代表一个行被索引值读的次数，如果值越低，表示索引得到的性能改善不高，因为索引不经常使用（这个值越高越好）。

Handler_read_next ：按照键顺序读下一行的请求数。如果你用范围约束或如果执行索引扫描来查询索引列，该值增加。

Handler_read_prev：按照键顺序读前一行的请求数。该读方法主要用于优化ORDER BY ... DESC。

Handler_read_rnd ：根据固定位置读一行的请求数。如果你正执行大量查询并需要对结果进行排序该值较高。你可能使用了大量需要MySQL扫描整个表的查询或你的连接没有正确使用键。这个值较高，意味着运行效率低，应该建立索引来补救。

Handler_read_rnd_next：在数据文件中读下一行的请求数。如果你正进行大量的表扫描，该值较高。通常说明你的表索引不正确或写入的查询没有利用索引。

4.4 sql优化 ​

大批量插入数据 ​

当使用load 命令导入数据的时候，适当的设置可以提高导入的效率。

对于 InnoDB 类型的表，有以下几种方式可以提高导入的效率：

1） 主键顺序插入

因为InnoDB类型的表是按照主键的顺序保存的，所以将导入的数据按照主键的顺序排列，可以有效的提高导入数据的效率。如果InnoDB表没有主键，那么系统会自动默认创建一个内部列作为主键，所以如果可以给表创建一个主键，将可以利用这点，来提高导入数据的效率。

2） 关闭唯一性校验

在导入数据前执行 SET UNIQUE_CHECKS=0，关闭唯一性校验，在导入结束后执行SET UNIQUE_CHECKS=1，恢复唯一性校验，可以提高导入的效率

3） 手动提交事务

如果应用使用自动提交的方式，建议在导入前执行 SET AUTOCOMMIT=0，关闭自动提交，导入结束后再执行 SET AUTOCOMMIT=1，打开自动提交，也可以提高导入的效率

优化insert语句 ​

当进行数据的insert操作的时候，可以考虑采用以下几种优化方案。

  • 如果需要同时对一张表插入很多行数据时，应该尽量使用多个值表的insert语句，这种方式将大大的缩减客户端与数据库之间的连接、关闭等消耗。使得效率比分开执行的单个insert语句快。

    示例， 原始方式为：

    sql
    insert into tb_test values(1,'Tom');
insert into tb_test values(2,'Cat');
insert into tb_test values(3,'Jerry');

    优化后的方案为 ：

    sql
    insert into tb_test values(1,'Tom'),(2,'Cat')，(3,'Jerry');

  • 在事务中进行数据插入。

    sql
    start transaction;
insert into tb_test values(1,'Tom');
insert into tb_test values(2,'Cat');
insert into tb_test values(3,'Jerry');
commit;

  • 数据有序插入

    sql
    insert into tb_test values(4,'Tim');
insert into tb_test values(1,'Tom');
insert into tb_test values(3,'Jerry');
insert into tb_test values(5,'Rose');
insert into tb_test values(2,'Cat');

    优化后

    sql
    insert into tb_test values(1,'Tom');
insert into tb_test values(2,'Cat');
insert into tb_test values(3,'Jerry');
insert into tb_test values(4,'Tim');
insert into tb_test values(5,'Rose');

优化order by语句 ​

1). 第一种是通过对返回数据进行排序，也就是通常说的 filesort 排序，所有不是通过索引直接返回排序结果的排序都叫 FileSort 排序。

2). 第二种通过有序索引顺序扫描直接返回有序数据，这种情况即为 using index，不需要额外排序，操作效率高。

尽量减少额外的排序，通过索引直接返回有序数据。where 条件和Order by 使用相同的索引，并且Order By 的顺序和索引顺序相同， 并且Order by 的字段都是升序，或者都是降序。否则肯定需要额外的操作，这样就会出现FileSort。

通过创建合适的索引，能够减少 Filesort 的出现，但是在某些情况下，条件限制不能让Filesort消失，那就需要加快 Filesort的排序操作。对于Filesort ， MySQL 有两种排序算法：

1） 两次扫描算法 ：MySQL4.1 之前，使用该方式排序。首先根据条件取出排序字段和行指针信息，然后在排序区 sort buffer 中排序，如果sort buffer不够，则在临时表 temporary table 中存储排序结果。完成排序之后，再根据行指针回表读取记录，该操作可能会导致大量随机I/O操作。

2）一次扫描算法：一次性取出满足条件的所有字段，然后在排序区 sort buffer 中排序后直接输出结果集。排序时内存开销较大，但是排序效率比两次扫描算法要高。

MySQL 通过比较系统变量 max_length_for_sort_data 的大小和Query语句取出的字段总大小， 来判定是否那种排序算法，如果max_length_for_sort_data 更大，那么使用第二种优化之后的算法；否则使用第一种。

可以适当提高 sort_buffer_size 和 max_length_for_sort_data 系统变量，来增大排序区的大小，提高排序的效率。

优化group by 语句 ​

由于GROUP BY 实际上也同样会进行排序操作，而且与ORDER BY 相比，GROUP BY 主要只是多了排序之后的分组操作。当然，如果在分组的时候还使用了其他的一些聚合函数，那么还需要一些聚合函数的计算。所以，在GROUP BY 的实现过程中，与 ORDER BY 一样也可以利用到索引。

如果查询包含 group by 但是用户想要避免排序结果的消耗， 则可以执行order by null 禁止排序。如下 ：

SQL
drop index idx_emp_age_salary on emp;

explain select age,count(*) from emp group by age;

优化后

sql
explain select age,count(*) from emp group by age order by null;

创建索引 ：

SQL
create index idx_emp_age_salary on emp(age,salary)；

优化嵌套查询 ​

Mysql4.1版本之后，开始支持SQL的子查询。这个技术可以使用SELECT语句来创建一个单列的查询结果，然后把这个结果作为过滤条件用在另一个查询中。使用子查询可以一次性的完成很多逻辑上需要多个步骤才能完成的SQL操作，同时也可以避免事务或者表锁死，并且写起来也很容易。但是，有些情况下，子查询是可以被更高效的连接（JOIN）替代。

示例 ，查找有角色的所有的用户信息 :

SQL
 explain select * from t_user where id in (select user_id from user_role );

优化后 :

SQL
explain select * from t_user u , user_role ur where u.id = ur.user_id;

连接(Join)查询之所以更有效率一些 ，是因为MySQL不需要在内存中创建临时表来完成这个逻辑上需要两个步骤的查询工作。

优化OR条件 ​

对于包含OR的查询子句，如果要利用索引，则OR之间的每个条件列都必须用到索引 ， 而且不能使用到复合索引； 如果没有索引，则应该考虑增加索引。

建议使用 union 替换 or ：

type 显示的是访问类型，是较为重要的一个指标，结果值从好到坏依次是：

system > const > eq_ref > ref > fulltext > ref_or_null  > index_merge > unique_subquery > index_subquery > range > index > ALL

UNION 语句的 type 值为 ref，OR 语句的 type 值为 range，可以看到这是一个很明显的差距

UNION 语句的 ref 值为 const，OR 语句的 type 值为 null，const 表示是常量值引用，非常快

这两项的差距就说明了 UNION 要优于 OR 。

优化分页查询 ​

1:在索引上完成排序分页操作，最后根据主键关联回原表查询所需要的其他列内容

2:该方案适用于主键自增的表，可以把Limit 查询转换成某个位置的查询

使用SQL提示 ​

SQL提示，是优化数据库的一个重要手段，简单来说，就是在SQL语句中加入一些人为的提示来达到优化操作的目的。

USE INDEX ​

在查询语句中表名的后面，添加 use index 来提供希望MySQL去参考的索引列表，就可以让MySQL不再考虑其他可用的索引。

create index idx_seller_name on tb_seller(name);
IGNORE INDEX ​

如果用户只是单纯的想让MySQL忽略一个或者多个索引，则可以使用 ignore index 作为 hint 。

 explain select * from tb_seller ignore index(idx_seller_name) where name = '小米科技';
FORCE INDEX ​

为强制MySQL使用一个特定的索引，可在查询中使用 force index 作为hint 。

SQL
create index idx_seller_address on tb_seller(address);

4.5 查询缓存优化 ​

开启Mysql的查询缓存，当执行完全相同的SQL语句的时候，服务器就会直接从缓存中读取结果，当数据被修改，之前的缓存会失效，修改比较频繁的表不适合做查询缓存。

查询缓存配置 ​

  1. 查看当前的MySQL数据库是否支持查询缓存：

    SQL
    SHOW VARIABLES LIKE 'have_query_cache';

  2. 查看当前MySQL是否开启了查询缓存 ：

    SQL
    SHOW VARIABLES LIKE 'query_cache_type';

  3. 查看查询缓存的占用大小 ：

    SQL
    SHOW VARIABLES LIKE 'query_cache_size';

  4. 查看查询缓存的状态变量：

    SQL
    SHOW STATUS LIKE 'Qcache%';

    各个变量的含义如下：

    参数含义
    Qcache_free_blocks查询缓存中的可用内存块数
    Qcache_free_memory查询缓存的可用内存量
    Qcache_hits查询缓存命中数
    Qcache_inserts添加到查询缓存的查询数
    Qcache_lowmen_prunes由于内存不足而从查询缓存中删除的查询数
    Qcache_not_cached非缓存查询的数量（由于 query_cache_type 设置而无法缓存或未缓存）
    Qcache_queries_in_cache查询缓存中注册的查询数
    Qcache_total_blocks查询缓存中的块总数

开启查询缓存 ​

MySQL的查询缓存默认是关闭的，需要手动配置参数 query_cache_type ， 来开启查询缓存。query_cache_type 该参数的可取值有三个 ：

值含义
OFF 或 0查询缓存功能关闭
ON 或 1查询缓存功能打开，SELECT的结果符合缓存条件即会缓存，否则，不予缓存，显式指定 SQL_NO_CACHE，不予缓存
DEMAND 或 2查询缓存功能按需进行，显式指定 SQL_CACHE 的SELECT语句才会缓存；其它均不予缓存

在 /usr/my.cnf 配置中，增加以下配置 ： 配置完毕之后，重启服务既可生效 ；

query_cache_type=1

查询缓存SELECT选项 ​

可以在SELECT语句中指定两个与查询缓存相关的选项 ：

SQL_CACHE : 如果查询结果是可缓存的，并且 query_cache_type 系统变量的值为ON或 DEMAND ，则缓存查询结果 。

SQL_NO_CACHE : 服务器不使用查询缓存。它既不检查查询缓存，也不检查结果是否已缓存，也不缓存查询结果。

例子：

SQL
SELECT SQL_CACHE id, name FROM customer;
SELECT SQL_NO_CACHE id, name FROM customer;

查询缓存失效的情况 ​

1） SQL 语句不一致的情况， 要想命中查询缓存，查询的SQL语句必须一致。

SQL
SQL1 : select count(*) from tb_item;
SQL2 : Select count(*) from tb_item;

2） 当查询语句中有一些不确定的时，则不会缓存。如 ： now() , current_date() , curdate() , curtime() , rand() , uuid() , user() , database() 。

SQL
SQL1 : select * from tb_item where updatetime < now() limit 1;
SQL2 : select user();
SQL3 : select database();

3） 不使用任何表查询语句。

SQL
select 'A';

4） 查询 mysql， information_schema或 performance_schema 数据库中的表时，不会走查询缓存。

SQL
select * from information_schema.engines;

5） 在存储的函数，触发器或事件的主体内执行的查询。

6） 如果表更改，则使用该表的所有高速缓存查询都将变为无效并从高速缓存中删除。这包括使用MERGE映射到已更改表的表的查询。一个表可以被许多类型的语句，如被改变 INSERT， UPDATE， DELETE， TRUNCATE TABLE， ALTER TABLE， DROP TABLE，或 DROP DATABASE 。

五、服务配置优化 ​

5.1 内存管理及优化 ​

内存优化原则 ​

1） 将尽量多的内存分配给MySQL做缓存，但要给操作系统和其他程序预留足够内存。

2） MyISAM 存储引擎的数据文件读取依赖于操作系统自身的IO缓存，因此，如果有MyISAM表，就要预留更多的内存给操作系统做IO缓存。

3） 排序区、连接区等缓存是分配给每个数据库会话（session）专用的，其默认值的设置要根据最大连接数合理分配，如果设置太大，不但浪费资源，而且在并发连接较高时会导致物理内存耗尽。

MyISAM 内存优化 ​

myisam存储引擎使用 key_buffer 缓存索引块，加速myisam索引的读写速度。对于myisam表的数据块，mysql没有特别的缓存机制，完全依赖于操作系统的IO缓存。

key_buffer_size ​

key_buffer_size决定MyISAM索引块缓存区的大小，直接影响到MyISAM表的存取效率。可以在MySQL参数文件中设置key_buffer_size的值，对于一般MyISAM数据库，建议至少将1/4可用内存分配给key_buffer_size。

在/usr/my.cnf 中做如下配置：

key_buffer_size=512M
read_buffer_size ​

如果需要经常顺序扫描myisam表，可以通过增大read_buffer_size的值来改善性能。但需要注意的是read_buffer_size是每个session独占的，如果默认值设置太大，就会造成内存浪费。

read_rnd_buffer_size ​

对于需要做排序的myisam表的查询，如带有order by子句的sql，适当增加 read_rnd_buffer_size 的值，可以改善此类的sql性能。但需要注意的是 read_rnd_buffer_size 是每个session独占的，如果默认值设置太大，就会造成内存浪费。

InnoDB 内存优化 ​

innodb用一块内存区做IO缓存池，该缓存池不仅用来缓存innodb的索引块，而且也用来缓存innodb的数据块。

innodb_buffer_pool_size ​

该变量决定了 innodb 存储引擎表数据和索引数据的最大缓存区大小。在保证操作系统及其他程序有足够内存可用的情况下，innodb_buffer_pool_size 的值越大，缓存命中率越高，访问InnoDB表需要的磁盘I/O 就越少，性能也就越高。

innodb_buffer_pool_size=512M
innodb_log_buffer_size ​

决定了innodb重做日志缓存的大小，对于可能产生大量更新记录的大事务，增加innodb_log_buffer_size的大小，可以避免innodb在事务提交前就执行不必要的日志写入磁盘操作。

innodb_log_buffer_size=10M

5.2 Mysql并发参数调整 ​

MySQL Server 是多线程结构，包括后台线程和客户服务线程。多线程可以有效利用服务器资源，提高数据库的并发性能。在Mysql中，控制并发连接和线程的主要参数包括 max_connections、back_log、thread_cache_size、table_open_cahce。

max_connections ​

采用max_connections 控制允许连接到MySQL数据库的最大数量，默认值是 151。如果状态变量 connection_errors_max_connections 不为零，并且一直增长，则说明不断有连接请求因数据库连接数已达到允许最大值而失败，这是可以考虑增大max_connections 的值。

Mysql 最大可支持的连接数，取决于很多因素，包括给定操作系统平台的线程库的质量、内存大小、每个连接的负荷、CPU的处理速度，期望的响应时间等。在Linux 平台下，性能好的服务器，支持 500-1000 个连接不是难事，需要根据服务器性能进行评估设定。

back_log ​

back_log 参数控制MySQL监听TCP端口时设置的积压请求栈大小。如果MySql的连接数达到max_connections时，新来的请求将会被存在堆栈中，以等待某一连接释放资源，该堆栈的数量即back_log，如果等待连接的数量超过back_log，将不被授予连接资源，将会报错。5.6.6 版本之前默认值为 50 ， 之后的版本默认为 50 + （max_connections / 5）， 但最大不超过900。

如果需要数据库在较短的时间内处理大量连接请求， 可以考虑适当增大back_log 的值。

table_open_cache ​

该参数用来控制所有SQL语句执行线程可打开表缓存的数量， 而在执行SQL语句时，每一个SQL执行线程至少要打开 1 个表缓存。该参数的值应该根据设置的最大连接数 max_connections 以及每个连接执行关联查询中涉及的表的最大数量来设定 ：

​ max_connections x N ；

thread_cache_size ​

为了加快连接数据库的速度，MySQL 会缓存一定数量的客户服务线程以备重用，通过参数 thread_cache_size 可控制 MySQL 缓存客户服务线程的数量。

innodb_lock_wait_timeout ​

该参数是用来设置InnoDB 事务等待行锁的时间，默认值是50ms ， 可以根据需要进行动态设置。对于需要快速反馈的业务系统来说，可以将行锁的等待时间调小，以避免事务长时间挂起； 对于后台运行的批量处理程序来说， 可以将行锁的等待时间调大， 以避免发生大的回滚操作。

六、安装 ​

6.1rpm安装 ​

RPM命令安装 ​

卸载软件rpm -e [软件包名]
安装软件rpm -ivh [软件包名]
在卸载过程中忽略依赖关系rpm -e --nodeps
在安装过程中忽略依赖关系rpm -ivh --nodeps
sh
# 1. 卸载Linux系统中已经安装的一个会导致冲突的软件包
rpm -e --nodeps mysql-libs-5.1.73-7.el6.x86_64（CentOS6）
rpm -e --nodeps mariadb-libs-1:5.5.56-2.el7.x86_64（CentOS7）
# ※注意：这个要卸载的软件包在CentOS6和CentOS7中不一样，要看具体冲突的是哪一个。

# 2. 执行MySQL服务器端程序的安装
rpm -ivh MySQL-server-5.5.52-1.el6.x86_64.rpm
# 3. 查看MySQL服务器端程序创建的系统用户和用户组
id mysql
# 4. 安装MySQL客户端
rpm -ivh MySQL-client-5.5.52-1.el6.x86_64.rpm
mysqladmin --version
# 5. 启动MySQL服务
systemctl start mysql.service
netstat -anp|grep 3306
# 6. 调用mysqladmin程序给MySQL服务器设置root账号的密码
mysqladmin -u root password
# 7. 登录MySQL服务器
mysql -u root -p

# 退出MySQL
mysql exit;
# 重启MySQL服务
service mysql restart;
systemctl restart mysql.service

## 拓展 修改MySQL服务器访问权限
# IDENTIFIED BY后面跟着的是root账号的**密码**。
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'root' WITH GRANT OPTION;

设置MySQL服务器默认字符集 ​

第一步：复制MySQL配置文件

cp /usr/share/mysql/my-small.cnf /etc/my.cnf

第二步：使用vim编辑器修改/etc/my.cnf

cnf
[mysqld]
port            = 3306
socket          = /var/lib/mysql/mysql.sock
skip-external-locking
key_buffer_size = 16K
max_allowed_packet = 1M
table_open_cache = 4
sort_buffer_size = 64K
read_buffer_size = 256K
read_rnd_buffer_size = 256K
net_buffer_length = 2K
thread_stack = 128K
character-set-server=utf8

第三步：重启MySQL服务

systemctl restart mysql.service

第四步：验证

shell
mysql> show variables like "%char%";
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

设置开机自启 ​

加入到系统服务：
chkconfig --add mysql
自动启动：
chkconfig mysql on

开启远程服务 ​

登录mysql:
grant all privileges on *.* to 'root' @'%' identified by '123456';
flush privileges;

6.2 docker安装 ​

shell
# 拉取mysql镜像
docker pull centos/mysql-57-centos7
# 创建容器
docker run -di --name=tensquare_mysql -p 3306:3306 -e MYSQL_ROOT_PASSWORD=123456 mysql
#-p 代表端口映射，格式为  宿主机映射端口:容器运行端口
#-e 代表添加环境变量  MYSQL_ROOT_PASSWORD  是root用户的登陆密码

Percona 为 MySQL 数据库服务器进行了改进，在功能和性能上较 MySQL 有着很显著的提升。该版本提升了在高负载情况下的 InnoDB 的性能、为 DBA 提供一些非常有用的性能诊断工具；另外有更多的参数和命令来控制服务器行为。 Percona Server 只包含 MySQL 的服务器版，并没有提供相应对 MySQL 的 Connector 和 GUI 工具进行改进。 Percona Server 使用了一些 google-mysql-tools, Proven Scaling, Open Query 对 MySQL 进行改造。 官网：https://www.percona.com/software/mysql-database

#镜像地址：https://hub.docker.com/_/percona/
#拉取镜像
docker pull percona:5.7.23
#创建容器
docker create --name percona -v /data/mysql-data:/var/lib/mysql -p 3306:3306 -e
MYSQL_ROOT_PASSWORD=root percona:5.7.23
#参数解释：
--name： percona 指定是容器的名称
-v： /data/mysql-data:/var/lib/mysql 将主机目录/data/mysql-data挂载到容器
的/var/lib/mysql上
-p： 33306:3306 设置端口映射，主机端口是33306，容器内部端口3306
-e： MYSQL_ROOT_PASSWORD=root 设置容器参数，设置root用户的密码为root
percona:5.7.23： 镜像名:版本
#启动容器
docker start percona

6.3 docker安装主从复制架构 ​

使用的MySQL版本依然是衍生版Percona，版本为5.7.23。并且通过docker进行搭建服务。

原理 ​

image-20210124231719198

mysql主(称master)从(称slave)复制的原理：

  • master将数据改变记录到二进制日志(binary log)中,也即是配置文件log-bin指定的文件(这些记录叫做二进制日志事件，binary log events)
  • slave将master的binary log events拷贝到它的中继日志(relay log)
  • slave重做中继日志中的事件,将改变反映它自己的数据(数据重演)

MySQL 复制的优点

  • 主库出现问题，可以快速切换到从库提供服务。

  • 可以在从库上执行查询操作，从主库中更新，实现读写分离，降低主库的访问压力。

  • 可以在从库中执行备份，以避免备份期间影响主库的服务。

主从配置需要注意的地方

  • 主DB server和从DB server数据库的版本一致
  • 主DB server和从DB server数据库数据一致
  • 主DB server开启二进制日志,主DB server和从DB server的server_id都必须唯一

主库配置文件my.conf ​

sh
#开启主从复制，主库的配置
log-bin = mysql-bin
#指定主库serverid
server-id=1
#指定同步的数据库，如果不指定则同步全部数据库
binlog-do-db=my_test

#是否只读,1 代表只读, 0 代表读写
read-only=0

#忽略的数据, 指不需要同步的数据库
binlog-ignore-db=mysql

# 重启mysql
service mysql restart ；
#执行SQL语句查询状态
SHOW MASTER STATUS
#File : 从哪个日志文件开始推送日志文件 
#Position ： 从哪个位置开始推送日志
#Binlog_Ignore_DB : 指定不需要同步的数据库

在主库创建同步用户 ​

sql
#授权用户slave01使用123456密码登录mysql
grant replication slave on *.* to 'slave01'@'127.0.0.1' identified by '123456';
#刷新配置
flush privileges;

从库配置文件my.conf ​

sh
#指定serverid，只要不重复即可，从库也只有这一个配置，其他都在SQL语句中操作
server-id=2
#指定binlog日志
log-bin=/var/lib/mysql/mysqlbin
#以下执行SQL：
CHANGE MASTER TO
 master_host='127.0.0.1',
 master_user='slave01',
 master_password='123456',
 master_port=3306,
 master_log_file='mysql-bin.000006',  
 master_log_pos=1120;
 
 # 重启服务器
 service mysql restart；
 #启动slave同步 停止为stop
 START SLAVE;
#查看同步状态
SHOW SLAVE STATUS;

搭建主库 ​

sh
#创建目录
mkdir /data/mysql/master01
cd /data/mysql/master01
mkdir conf data
chmod 777 * -R

#创建配置文件
cd /data/mysql/master01/conf
vim my.cnf

#输入如下内容
[mysqld]
log-bin=mysql-bin  #开启二进制日志
server-id=1  #服务id，不可重复

#创建容器
docker create --name percona-master01 -v /data/mysql/master01/data:/var/lib/mysql -v /data/mysql/master01/conf:/etc/my.cnf.d -p 3306:3306 -e MYSQL_ROOT_PASSWORD=root percona:5.7.23

#启动
docker start percona-master01 && docker logs -f percona-master01

#创建同步账户以及授权
create user 'itcast'@'%' identified by 'itcast';
grant replication slave on *.* to 'itcast'@'%';
flush privileges;

#出现 [Err] 1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause and错误解决方案，在my.cnf配置文件中设置
sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO ,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'

#查看master状态
show master status;

#查看二进制日志相关的配置项
show global variables like 'binlog%';

#查看server相关的配置项
show global variables like 'server%';

master状态：

image-20210124232159603

image-20210124232209238

image-20210124232216754

搭建从库 ​

sh
#创建目录
mkdir /data/mysql/slave01 
cd /data/mysql/slave01 
mkdir conf data
chmod 777 * -R

#创建配置文件
cd /data/mysql/slave01/conf 
vim my.cnf

#输入如下内容
[mysqld]
server-id=2  #服务id，不可重复
sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO ,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'

#创建容器
docker create --name percona-slave01 -v /data/mysql/slave01/data:/var/lib/mysql -v 
/data/mysql/slave01/conf:/etc/my.cnf.d -p 3307:3306 -e MYSQL_ROOT_PASSWORD=root percona:5.7.23

#启动
docker start percona-slave01 && docker logs -f percona-slave01

#设置master相关信息
CHANGE MASTER TO
 master_host='192.168.1.18',  master_user='itcast',
 master_password='itcast',
 master_port=3306,
 master_log_file='mysql-bin.000002', 
 master_log_pos=648;
 
#启动同步
start slave;

#查看master状态
show slave status;

可以进行创建数据库、表，插入数据等操作，可以发现实现了主从。

主从复制模式 ​

image-20210124232554629

在查看二进制日志相关参数内容中，会发现默认的模式为ROW，其实在MySQL中提供了有3种模式，基于SQL语句的复制(statement-based replication, SBR)，基于行的复制(row-based replication, RBR)，混合模式复制(mixed-based replication, MBR)。对应的，binlog的格式也有三种：STATEMENT，ROW，MIXED。

STATEMENT模式（SBR）

每一条会修改数据的sql语句会记录到binlog中。

  • 优点是并不需要记录每一条sql语句和每一行的数据变化，减少了binlog日志量，节约IO，提高性能。
  • 缺点是在某些情况下会导致master-slave中的数据不一致(如sleep()函数， last_insert_id()，以及user-defined functions(udf)等会出现问题)

ROW模式（RBR）

不记录每条sql语句的上下文信息，仅需记录哪条数据被修改了，修改成什么样了。而且不会出现某些特定情况下的存储过程、或function、或trigger的调用和触发无法被正确复制的问题。缺点是会产生大量的日志，尤其是alter table的时候会让日志暴涨。

MIXED模式（MBR）

以上两种模式的混合使用，一般的复制使用STATEMENT模式保存binlog，对于STATEMENT模式无法复制的操作使用ROW模式保存binlog，MySQL会根据执行的SQL语句选择日志保存方式。

建议使用MIXED模式。

#修改主库的配置
binlog_format=MIXED

#重启
docker restart percona-master01 && docker logs -f percona-master01

#查看二进制日志相关的配置项
show global variables like 'binlog%';

image-20210124232759597